package com.motionlog.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteQueryBuilder;
import android.os.Environment;

public class Database {

	private static final String TAG = Database.class.getSimpleName();
	private static String DB_PATH = Environment.getExternalStorageDirectory()
			+ "/Android/data/com.motionlog/databases/";
	private static String DB_NAME = "motion.db";
	private static String DB_BACK_UP_NAME = "motion.db.bak";

	private SQLiteDatabase myDataBase;
	private final Context myContext;

	public Database(Context context) {
		myContext = context;
		try {
			createDataBase();
			// copyDataBase();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static Database sDatabase;

	public static Database getInstance(Context context) {
		if (sDatabase == null) {
			sDatabase = new Database(context);
		}
		return sDatabase;
	}

	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
			//
		} else {
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (Exception e) {
		}

		if (checkDB != null) {
			checkDB.close();
		}

		return checkDB != null ? true : false;
	}

	private void cpFile(String from, String to) throws IOException {
		String inFileName = from;
		InputStream myInput = new FileInputStream(inFileName);

		String outFileName = to;
		File file = new File(outFileName);
		File dir = new File(file.getParent());
		if (!dir.isDirectory()) {
			if (!dir.mkdirs()) {
				android.util.Log.i(TAG,
						"mkdir failed. dir: " + file.getParent());
				return;
			}
		}
		if (!file.exists()) {
			try {
				file.createNewFile();
			} catch (IOException e) {
				android.util.Log.i(TAG, "create file failed.");
				return;
			}
		}

		OutputStream myOutput = new FileOutputStream(file);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void restoreDatabase() throws IOException {
		cpFile(DB_PATH + DB_BACK_UP_NAME, DB_PATH + DB_NAME);
	}

	public void backupDatabase() throws IOException {
		cpFile(DB_PATH + DB_NAME, DB_PATH + DB_BACK_UP_NAME);
	}

	public void copyDataBase() throws IOException {
		InputStream myInput = null;
		myInput = myContext.getAssets().open(DB_NAME);

		String outFileName = DB_PATH + DB_NAME;

		File file = new File(outFileName);
		File dir = new File(file.getParent());
		if (!dir.isDirectory()) {
			if (!dir.mkdirs()) {
				android.util.Log.i(TAG,
						"mkdir failed. dir: " + file.getParent());
				return;
			}
		}
		if (!file.exists()) {
			try {
				file.createNewFile();
			} catch (IOException e) {
				android.util.Log.i(TAG, "create file failed.");
				return;
			}
		}

		OutputStream myOutput = new FileOutputStream(file);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void openDataBase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);
	}

	SQLiteDatabase mSQLiteDatabase;

	public SQLiteDatabase getWritableDatabase() {
		String myPath = DB_PATH + DB_NAME;
		mSQLiteDatabase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
		return mSQLiteDatabase;
	}

	public SQLiteDatabase getReadableDatabase() {
		if (mSQLiteDatabase != null) {
			return mSQLiteDatabase;
		} else {
			return getWritableDatabase();
		}
	}

	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();
	}

	public long insertMotionLike(String table, String name) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		long id = getWritableDatabase().insert(table, null, cv);
		return id;
	}

	public boolean insertMotionLike(String table, String name, SQLiteDatabase db) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		long id = db.insert(table, null, cv);
		return id == -1 ? false : true;
	}

	public Cursor selectMotionLike(String table) {
		return getReadableDatabase().query(table,
				new String[] { "_id", "name", "content" }, null, null, null,
				null, null);
	}

	public List<? extends Base> getMotions(Class clz) {
		List<Base> motions = new ArrayList<Base>();
		Cursor cursor = selectMotionLike(clz.getSimpleName().toLowerCase());
		while (cursor.moveToNext()) {
			Base base = new Base();
			base.id = cursor.getInt(0);
			base.name = cursor.getString(1);
			base.content = cursor.getString(2);
			motions.add(base);
		}

		return motions;
	}

	public long insertLog(int motionId, int level, String reasonSummary) {
		ContentValues cv = new ContentValues();
		cv.put("motion_id", motionId);
		cv.put("level", level);
		cv.put("reason_summary", reasonSummary);
		long id = getWritableDatabase().insert("log", null, cv);
		return id;
	}

	public int updateLog(long logId, String description, String markup,
			String methodSummary) {
		ContentValues cv = new ContentValues();
		cv.put("motion_reason_description", description);
		cv.put("method_markup", markup);
		cv.put("method_summary", methodSummary);
		int rowEffected = getWritableDatabase().update("log", cv, "_id = ?",
				new String[] { String.valueOf(logId) });
		return rowEffected;
	}

	public int updateLog(long logId, String nowThink, String categorySummary) {
		ContentValues cv = new ContentValues();
		cv.put("now_think", nowThink);
		cv.put("category_summary", categorySummary);
		int rowEffected = getWritableDatabase().update("log", cv, "_id = ?",
				new String[] { String.valueOf(logId) });
		return rowEffected;
	}

	/**
	 * 
	 * @param logId
	 * @param setp
	 *            1 应对方法 2 分类 3 未解决
	 * @return
	 */
	public int updateLogFinishStep(long logId, int step) {
		ContentValues cv = new ContentValues();
		cv.put("finish_step", step);
		int rowEffected = getWritableDatabase().update("log", cv, "_id = ?",
				new String[] { String.valueOf(logId) });
		return rowEffected;
	}

	public long insertReasonLog(long logId, int reasonId) {
		ContentValues cv = new ContentValues();
		cv.put("log_id", logId);
		cv.put("reason_id", reasonId);
		return getWritableDatabase().insert("log_reason", null, cv);
	}

	public long insertMethodLog(long logId, int methodId) {
		ContentValues cv = new ContentValues();
		cv.put("log_id", logId);
		cv.put("method_id", methodId);
		return getWritableDatabase().insert("log_method", null, cv);
	}

	public long insertCategoryLog(long logId, int categoryId) {
		ContentValues cv = new ContentValues();
		cv.put("log_id", logId);
		cv.put("category_id", categoryId);
		return getWritableDatabase().insert("log_category", null, cv);
	}

	private String[] getBeforeWeekDateString(int week) {
		Date now = new Date();
		long ms = now.getTime() - (long) week * 7 * 24 * 60l * 60l * 1000l;
		now.setTime(ms);
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String d = format.format(now);
		android.util.Log.d(TAG, "date:  " + d);
		return new String[] { d };
	}

	public int getLogCount(int week) {
		String sql = "select count(*) as count from log where date(time) >= ?";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getInt(0);
		}
		c.close();

		return 0;
	}

	public int getMotionLogCount(int moitionId, int week) {
		String sql = "select motion_id, motion.name as motion_name, date(time) as date, "
				+ "count(motion_id) from log left outer join motion on (motion._id = log.motion_id) "
				+ "where date(time) >= ? and motion._id == ?";
		android.util.Log.d(TAG, "sql: " + sql);

		Cursor c = getReadableDatabase().rawQuery(
				sql,
				new String[] { getBeforeWeekDateString(week)[0],
						String.valueOf(moitionId) });
		int count = 0;
		if (c.moveToFirst()) {
			count = c.getInt(3);
		}
		c.close();
		return count;
	}

	public String[] getMotionStatistic(int week) {
		String sql = "select motion_id, motion.name as motion_name, date(time) as date, "
				+ "count(motion_id) from log left outer join motion on (motion._id = log.motion_id) "
				+ "where date(time) >= ? group by motion_id order by count(motion_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		android.util.Log.d(TAG, "cursor.coutn: " + c.getCount());
		String[] motions = new String[2];
		for (int i = 0; i < motions.length; i++) {
			if (c.moveToNext()) {
				motions[i] = c.getString(1);
				android.util.Log.d(TAG, "m: " + motions[i]);
			} else {
				motions[i] = "";
			}
		}
		c.close();
		return motions;
	}

	public String getMainReason(int week) {
		String sql = "select reason.name from log_reason left outer join reason on "
				+ "(reason._id = log_reason.reason_id) where date(time) >= ? "
				+ "group by reason_id order by count(reason_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getString(0);
		}
		c.close();
		return "";
	}

	public String getMainMethod(int week) {
		String sql = "select method.name from log_method left outer join method on "
				+ "(method._id = log_method.method_id) where date(time) >= ? "
				+ "group by method_id order by count(method_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getString(0);
		}
		c.close();
		return "";
	}

	public String getMainCategory(int week) {
		String sql = "select category.name from log_category left outer join category on "
				+ "(category._id = log_category.category_id) where date(time) >= ? "
				+ "group by category_id order by count(category_id) desc";

		Cursor c = getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
		if (c.moveToFirst()) {
			return c.getString(0);
		}
		c.close();
		return "";
	}

	public Cursor getFinishMethodCount(int week) {
		String sql = "select finish_step, count(finish_step) from log "
				+ "where date(time) >= ? group by finish_step";

		return getReadableDatabase().rawQuery(sql,
				getBeforeWeekDateString(week));
	}

	public String getReasonSummary(long logId) {
		String sql = "select name from log_reason left outer join reason on "
				+ "(log_reason.reason_id = reason._id) where log_reason.log_id = ?";

		Cursor c = getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(logId) });
		StringBuilder sb = new StringBuilder();
		while (c.moveToNext()) {
			sb.append(c.getString(0));
		}

		return sb.toString();
	}

	public Cursor selectLog(long id) {
		String sql = "select name, reason_summary, motion_reason_description,"
				+ "method_summary,method_markup,now_think,category_summary,level "
				+ "from log left outer join motion on "
				+ "(motion._id = log.motion_id) where log._id = ?";
		return getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(id) });
	}

	public Cursor selectLog(Date date) {
		String query = SQLiteQueryBuilder
				.buildQueryString(
						true,
						"log LEFT OUTER JOIN motion ON (motion._id = log.motion_id)",
						new String[] { "date(time) as date, time(time) as time, "
								+ "time as datetime, motion.name as motion_name, level, "
								+ "log._id as _id" }, "date(time) = ?", null,
						null, null, null);
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String d = format.format(date);
		android.util.Log.d(TAG, "d: " + d + ", query: " + query);
		// 02-18 10:17:34.742: D/Database(14544): d: 2012-2-18, query: SELECT
		// DISTINCT date(time) as date, time(time) as time, time as datetime,
		// motion.name as motion_name, level, log._id as _id FROM log LEFT OUTER
		// JOIN motion ON (motion._id = log.motion_id) WHERE date(time) = ?

		return getReadableDatabase().rawQuery(query, new String[] { d });
		// SQLiteQueryBuilder s = new SQLiteQueryBuilder();
		// s.settfoo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)")
		// return getReadableDatabase().query("log", null, null, null, null,
		// null,
		// null);
	}

	public Cursor selectSaying() {
		return getReadableDatabase().query("saying", null, null, null, null,
				null, null);
	}

	public Saying getRandomSaying() {
		Cursor cursor = selectSaying();
		Random random = new Random();
		int count = cursor.getCount();
		int id = random.nextInt(count) + 1;
		android.util.Log.d(TAG, "_id: " + id + ", count: " + count);
		cursor.close();
		Cursor c = getReadableDatabase().query("saying", null, "_id = ?",
				new String[] { String.valueOf(id) }, null, null, null);
		Saying saying = new Saying();
		if (c.moveToNext()) {
			saying.id = c.getInt(0);
			saying.author = c.getString(1);
			saying.content = c.getString(2);
		}
		return saying;
	}
}
